#include "DBService.h"

#include <QDebug>

DBService* DBService::instance = new DBService;

DBService::DBService()
{
    // 初始化数据库
    // 数据库的表格  时间戳作为主键， 年月日时分秒，主题，内容
}

DBService::~DBService()
{
    if(m_connect)
        disconnectDatabase();
}

DBService *DBService::getInstance()
{
    return instance;
}

void DBService::releaseInstance()
{
    if(instance)
        delete instance;
}

bool DBService::isConnect()
{
    return m_connect;
}

bool DBService::insertData(const QJsonObject &data)
{
    if(!m_connect){
        qDebug() << "not connect db";
        return false;
    }

    // 获取表中的属性
    QString time = data["time"].toString();
    QString topic = data["topic"].toString();
    QString context = data["context"].toString();

    // 向数据库中插入数据
    QString insertSQL = R"(
                        INSERT INTO NotebookTable (time, topic, context)
                        VALUES (:time, :topic,:context);
                        )";

    QSqlQuery query(m_db);
    query.prepare(insertSQL);

    // 绑定参数
    query.bindValue(":time", time);
    query.bindValue(":topic", topic);
    query.bindValue(":context", context);

    if (!query.exec()) {
        qDebug() << "Error inserting data:" << query.lastError().text();
        return false;
    }

    return true;
}


/**
 * @brief condition 查询条件
 * // 时间查询
 * condition["type"] 1天，1周，1月，半年，1年，自定义
 * condition["startTime"]
 * condition["endTime"]
 * Topic查询
 * Topic模糊查询 condition["type"] = "topic"
 */
bool DBService::searchData(const QJsonObject &condition, QJsonObject &result)
{
    QSqlQuery query(m_db);
    QString sql;
    QString startTime, endTime;

    QString type = condition["type"].toString();
    // 根据条件生成 SQL 查询语句
    if (type == DBServicePublic::ONE_DAY) {
        sql = "SELECT * FROM NotebookTable WHERE time >= strftime('%Y-%m-%d %H:%M:%S', 'now', '-1 day')";
    } else if (type == DBServicePublic::ONE_WEEK) {
        sql = "SELECT * FROM NotebookTable WHERE time >= strftime('%Y-%m-%d %H:%M:%S', 'now', '-7 days')";
    } else if (type == DBServicePublic::ONE_MONTH) {
        sql = "SELECT * FROM NotebookTable WHERE time >= strftime('%Y-%m-%d %H:%M:%S', 'now', '-1 month')";
    } else if (type == DBServicePublic::HALF_YEAR) {
        sql = "SELECT * FROM NotebookTable WHERE time >= strftime('%Y-%m-%d %H:%M:%S', 'now', '-6 months')";
    } else if (type == DBServicePublic::ONE_YEAR) {
        sql = "SELECT * FROM NotebookTable WHERE time >= strftime('%Y-%m-%d %H:%M:%S', 'now', '-1 year')";
    } else if (type == DBServicePublic::CUSTOM_DATE) {
        startTime = condition["startTime"].toString();
        endTime = condition["endTime"].toString();
        sql = "SELECT * FROM NotebookTable WHERE time BETWEEN :startTime AND :endTime";
    } else if (type == DBServicePublic::TOPIC_NAME) {
        QString topic = condition["topic"].toString();
        sql = "SELECT * FROM NotebookTable WHERE topic LIKE :topic";
    } else {
        qDebug() << "Invalid condition type.";
        return false;
    }

    // 执行查询
    query.prepare(sql);

    if (type == DBServicePublic::CUSTOM_DATE) {
        query.bindValue(":startTime", startTime);
        query.bindValue(":endTime", endTime);
    } else if (type == DBServicePublic::TOPIC_NAME) {
        query.bindValue(":topic", "%" + condition["topic"].toString() + "%");
    }

    if (!query.exec()) {
        qDebug() << "Error: Failed to execute query." << query.lastError().text();
        return false;
    }

    // 处理查询结果
    QJsonArray resultsArray;
    while (query.next()) {
        QJsonObject event;
        event["context"] = query.value("context").toString();
        event["time"] = query.value("time").toString();
        event["topic"] = query.value("topic").toString();
        resultsArray.append(event);
    }

    result["results"] = resultsArray;
    return true;
}

bool DBService::createTable(const QString &tableName, const QJsonObject &tableAttr)
{
    if(!m_connect){
        qDebug() << "not connect db";
        return false;
    }
    // tableAttr key作为属性名，value作为数据类型
    // 检查表是否存在
    QString checkTableExistsSQL = QString("SELECT name FROM sqlite_master WHERE type='table' AND name='%1';").arg(tableName);
    QSqlQuery query(m_db);
    if (!query.exec(checkTableExistsSQL)) {
        qDebug() << "Error checking table existence:" << query.lastError().text();
        return false;
    }

    // 如果表已存在，直接返回
    if (query.next()) {
        qDebug() << "Table" << tableName << "already exists.";
        return true;
    }

    // 表不存在，根据tableAttr生成CREATE TABLE语句
    QString createTableSQL = QString("CREATE TABLE %1 (").arg(tableName);

    // 遍历QJsonObject，生成字段定义
    for (auto it = tableAttr.begin(); it != tableAttr.end(); ++it) {
        QString columnName = it.key();
        QString columnType = it.value().toString();

        // 添加字段到SQL语句
        createTableSQL += QString("%1 %2, ").arg(columnName, columnType);
    }

    // 去掉最后一个逗号并闭合括号
    createTableSQL.chop(2);
    createTableSQL += ");";

    qDebug() << "Create Table SQL:" << createTableSQL;

    // 执行CREATE TABLE语句
    if (!query.exec(createTableSQL)) {
        qDebug() << "Error creating table:" << query.lastError().text();
        return false;
    }

    qDebug() << "Table" << tableName << "created successfully.";
    return true;
}

bool DBService::connectDatabase()
{
    if(m_connect){
        return true;
    }
    // 添加 SQLite 驱动
    m_db = QSqlDatabase::addDatabase("QSQLITE");
    // 设置数据库文件名
    QString dbPath = QCoreApplication::applicationDirPath() + "/NotebookDB.db";
    qDebug() << dbPath;
    m_db.setDatabaseName(dbPath);

    // 打开数据库
    if (!m_db.open()) {
        qDebug() << "Error: unable to open database";
        return false;
    }

    m_connect = true;

    return true;
}

bool DBService::disconnectDatabase()
{
    m_db.close();
    m_connect = false;
    return true;
}


